Skip Headers

Oracle® Database Upgrade Guide 10g Release 2 (10.2) Part Number B14238-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# B Migrating from Server Manager to SQL*Plus

This appendix guides you through the process of modifying your Server Manager line mode scripts to work with SQL*Plus. Server Manager is not supported in Oracle9`i` release 9.0.1 and later. If you run SQL scripts using Server Manager line mode, then you will need to change these scripts so that they are compatible with SQL*Plus, and then run them using SQL*Plus.

This appendix covers the following topics:

- Startup Differences

- Commands

- Syntax Differences

See Also: SQL*Plus User's Guide and Reference for detailed information about SQL*Plus

Note: For brevity, Server Manager line mode is referred to as Server Manager in the rest of this appendix.

## Startup Differences

The methods for starting Server Manager and SQL*Plus are different, and your SQL scripts must be modified to properly start SQL*Plus. The following sections explain the startup differences and provide options for starting SQL*Plus.

### Starting Server Manager

To start Server Manager, enter the name of the Server Manager program at a system prompt; the name of this program is operating system-specific. After you start up Server Manager, connect using the `CONNECT` command, as in the following example:

    CONNECT hr/hr

### Starting SQL*Plus

The following sections describe various ways to start SQL*Plus.

#### Starting SQL*Plus with the NOLOG Option

If you want SQL*Plus to behave in the same way as Server Manager, then use the `NOLOG` option when you start SQL*Plus, as in the following example:

    sqlplus /nolog

SQL*Plus starts and you can use the `CONNECT` command to connect as a user.

#### Starting SQL*Plus with Connect Information

Another option for starting SQL*Plus is to enter the connect information when you start the program. For example, to start SQL*Plus and connect as user `hr` with password `hr`, enter the following:

    sqlplus hr/hr

SQL*Plus starts and connects as user `hr`.

#### Starting SQL*Plus without Options or Connect Information

To start SQL*Plus without options or connect information, enter the following:

    sqlplus

SQL*Plus prompts you for a user name and password. When you enter a valid user name and password, SQL*Plus starts and connects as the user you specified at the prompts. In your SQL scripts, however, you may not want to prompt the user to enter a user name and password.

## Commands

Server Manager and SQL*Plus share certain commands that behave the same in both programs. Other commands, however, behave differently in SQL*Plus than they do in Server Manager. To successfully migrate from Server Manager to SQL*Plus, you need to understand these differences and similarities. The following sections include information about modifying your SQL scripts to use commands that are interpreted correctly by SQL*Plus.

### Commands Introduced in SQL*Plus Release 8.1

Table B-1 lists Server Manager commands that are available in SQL*Plus release 8.1 and higher. You can use these commands in SQL scripts that you run with SQL*Plus.

Note: If you run SQL scripts containing any of these commands in Oracle7 or release 8.0, then you must use Server Manager to run these scripts. Versions of SQL*Plus before SQL*Plus release 8.1 will not run scripts containing these commands.

Table B-1 Commands Introduced in SQL*Plus Release 8.1

Command | Description
--- | ---
`ARCHIVE LOG` | Starts or stops automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about archives.
`RECOVER` | Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database.
`SET AUTORECOVERY` | `ON` causes the `RECOVER` command to automatically apply the default filenames of archived redo log files needed during recovery. No interaction is needed when `AUTORECOVERY` is set to `ON`, provided the necessary files are in the expected locations with the expected names.
`SET INSTANCE` | Changes the default instance for your session to the specified instance path. Does not connect to a database. The default instance is used for commands when no instance is specified.
`SET LOGSOURCE` | Specifies the location from which archive logs are retrieved during recovery. The default value is set by the `LOG_ARCHIVE_DEST` initialization parameter. Issuing the `SET LOGSOURCE` command without a pathname restores the default location.
`SHOW AUTORECOVERY` | Shows whether autorecovery is enabled.
`SHOW INSTANCE` | Shows the connect string for the default instance. `SHOW INSTANCE` returns the value `LOCAL` if you have not used `SET INSTANCE` or if you have used the `LOCAL` option of the `SET INSTANCE` command.
`SHOW LOGSOURCE` | Shows the current setting of the archive log location. Displays `DEFAULT` if the default setting is in effect, as specified by the `LOG_ARCHIVE_DEST` initialization parameter.
`SHOW PARAMETERS` | Displays the current values of one or more initialization parameters. The `SHOW PARAMETERS` command, without any string following the command, displays all initialization parameters.
`SHOW SGA` | Displays information about the current instance's System Global Area.
`SHUTDOWN` | Shuts down a currently running Oracle instance, optionally closing and dismounting a database. Note: The `STARTUP` and `SHUTDOWN` commands in SQL*Plus release 8.1 are not supported against an Oracle7 server.
`STARTUP` | Starts an Oracle instance with several options, including mounting and opening a database. Note: The `STARTUP` and `SHUTDOWN` commands in SQL*Plus release 8.1 are not supported against an Oracle7 server.

### Commands Common to Server Manager and SQL*Plus

The commands listed in Table B-2 are available in both Server Manager and SQL*Plus, and have been available in both programs in past releases of Oracle. You do not need to alter these commands in your SQL scripts to use SQL*Plus.

Note: There may be minor formatting differences in the output for these commands in the two programs.

Table B-2 Server Manager Commands Corresponding to Existing SQL*Plus Commands

Command | Description
--- | ---
`CONNECT` | Connects to a database using the specified user name.
`DESCRIBE` | Describes a function, package, package body, procedure, table, view, or object type. For example, for a table, displays the definitions of each column in the table.
`REMARK` | Enters a comment, typically in SQL script files.
`SET COMPATIBILITY` | Sets compatibility mode to `V7`, `V8`, or `NATIVE`. The compatibility mode setting affects the specification of character columns, integrity constraints, and rollback segment storage parameters. `NATIVE` matches the version of the database.
`SET ECHO` | Controls whether the `START` command lists each command in a command file as the command is executed. `ON` lists the commands; `OFF` suppresses the listing.
`SET NUMWIDTH` | Sets the default width for displaying numbers.
`SET SERVEROUTPUT` | Controls whether to display the output (that is, `DBMS_OUTPUT.PUT_LINE`) of stored procedures or PL/SQL blocks in SQL*Plus. `OFF` suppresses the output of `DBMS_OUTPUT.PUT_LINE`; `ON` displays the output.
`SET TERMOUT` | Controls the display of output generated by commands executed from a command file. `OFF` suppresses the display so that you can spool output from a command file without seeing the output on the screen. `ON` displays the output.
`SHOW ALL` | Lists all of the system variables set by the `SET` command in alphabetical order, except ERRORS, PARAMETERS, and SGA.
`SHOW ERRORS` | Shows the errors generated from the last compilation of a procedure, package, or function, if any.
`SPOOL` | Stores query results in an operating system file and, optionally in SQL*Plus, sends the file to a printer. Note: The extension of spool files may differ between SQL*Plus and Server Manager. To ensure an extension, specify it when you issue the `SPOOL` command. Also, SQL*Plus may format white space in terminal output using tab characters in place of repeated spaces. Use `SET TAB OFF` in SQL*Plus to prevent this replacement. Server Manager never outputs tab characters.

### SQL*Plus Equivalents for Server Manager Commands

Table B-3 lists the SQL*Plus commands that correspond to Server Manager commands with different names. If you are using any of these Server Manager commands in SQL scripts, then modify the scripts to use the SQL*Plus commands instead.

Table B-3 SQL*Plus Equivalents for Server Manager Commands

Server Manager Commands | SQL*Plus Commands | Description
--- | --- | ---
`SET CHARWIDTH` `SET DATEWIDTH` `SET LONGWIDTH` | `COLUMN FORMAT` | You can use the `COLUMN FORMAT` command in SQL*Plus to set the column width of character columns, date columns, and number columns. In your SQL scripts, replace the `SET CHARWIDTH`, `SET DATEWIDTH`, and `SET LONGWIDTH` Server Manager commands with the SQL*Plus `COLUMN FORMAT` command. Use `COLUMN FORMAT` for all character columns to be changed. There is no equivalent command to change all character columns with one command. For example, suppose you have the following entry in a SQL script:
    SET CHARWIDTH 5
This command sets the width for all character columns to 5 in Server Manager. To specify that a particular column, such as `first_name`, display with a width of 5 characters, enter the following SQL*Plus command:
    COLUMN first_name FORMAT A5
Use `COLUMN FORMAT` for all character columns to be changed. There is no equivalent command to change all character columns with one command. Use `COLUMN FORMAT` for all date columns to be changed. There is no equivalent command to change all date columns with one command. Use `SET LONG` to specify how much of the `LONG` column to fetch and display.
`SET STOPONERROR` | `WHENEVER SQLERROR` `WHENEVER OSERROR` | Use the `WHENEVER SQLERROR` and `WHENEVER OSERROR` commands to direct SQL*Plus to either exit or continue whenever a SQL error or operating system error occurs. Use these commands in your SQL scripts instead of the Server Manager `SET STOPONERROR` command. For both `WHENEVER SQLERROR` and `WHENEVER OSERROR`, the `EXIT` clause directs SQL*Plus to exit, while the `CONTINUE` clause directs SQL*Plus to continue. Other terms and clauses are also available for these commands.

### Possible Differences in the SET TIMING Command

The `SET TIMING` command is available in both Server Manager and SQL*Plus, but this command may function differently in the two programs on some operating systems. Check your operating system-specific Oracle documentation for more information. If the `SET TIMING` command functions differently in these two programs on your operating system, then modify your SQL scripts so that this command functions properly with SQL*Plus.

### Server Manager Commands Unavailable in SQL*Plus

The following Server Manager commands are unavailable in SQL*Plus release 8.1 and higher:

- `SET MAXDATA`

- `SET RETRIES`

Remove these commands from your SQL scripts.

## Syntax Differences

The following sections explain the syntax differences between Server Manager and SQL*Plus. Modify your SQL scripts to conform with SQL*Plus syntax conventions before you attempt to run your scripts using SQL*Plus.

### Comments

SQL*Plus recognizes the following types of comments:

- the SQL*Plus REMARK command (or REM)

- the SQL comment delimiters, /* ... */

- the ANSI/ISO comments, --

The SQL*Plus User's Guide and Reference provides detailed information about using these types of comments in SQL*Plus scripts.

Server Manager supports these types of comments, but the behavior is different for some of them. Also, certain types of comments are available in Server Manager, but not in SQL*Plus. The sections below discuss each type of comment and the syntax differences between Server Manager and SQL*Plus.

#### REMARK Command (or REM)

In general, the `REMARK` command works the same in Server Manager and SQL*Plus, and you do not need to change the occurrences of the `REMARK` command in your SQL scripts. There is, however, one difference: SQL*Plus interprets a hyphen that terminates a `REMARK` command differently than Server Manager. See "Hyphens Used as Dividing Lines" for information about this difference.

#### SQL Comment Delimiters, /* ... */

In Server Manager, the SQL comment delimiters can be placed after a semicolon (;), but in SQL*Plus, placing a SQL comment delimiter after a semicolon is not allowed. Except for this one difference, SQL comment delimiters work the same in Server Manager and SQL*Plus.

If your SQL scripts contain any SQL comment delimiters placed after a semicolon, then either move the comment to its own line, or remove the semicolon and place a slash (/) on the next line to end the SQL statement.

For example, suppose you have the following Server Manager code in one of your SQL scripts:

    SELECT * FROM hr.employees
        WHERE job_id LIKE '%CLERK'; /* Includes only clerks. */

In SQL*Plus, replace this code with either of the following entries:

    SELECT * FROM hr.employees
        WHERE job_id LIKE '%CLERK';
    /* Includes only clerks. */

    SELECT * FROM hr.employees
        WHERE job_id LIKE '%CLERK' /* Includes only clerks. */
        /

#### ANSI/ISO Comments, --

In Server Manager, the ANSI/ISO comments can be placed after a semicolon (;), but in SQL*Plus, placing an ANSI/ISO comment after a semicolon is not allowed. Except for this one difference, ANSI/ISO comments work the same in Server Manager and SQL*Plus.

If your SQL scripts contain any ANSI/ISO comments that are placed after a semicolon, then either move the comment to its own line, or remove the semicolon and place a slash (/) on the next line to end the SQL statement.

For example, suppose you have the following Server Manager code in one of your SQL scripts:

    SELECT * FROM hr.employees
        WHERE job_id LIKE '%CLERK'; -- Includes only clerks.

In SQL*Plus, replace this code with either of the following entries:

    SELECT * FROM hr.employees
        WHERE job_id LIKE '%CLERK';
    -- Includes only clerks.

    SELECT * FROM hr.employees
        WHERE job_id LIKE '%CLERK' -- Includes only clerks.
        /

#### Server Manager Pound (#) Comments

Server Manager supports the use of the pound sign (#) to indicate a comment line. If your scripts contain these comments, then change the '#' to '--' to run the scripts using SQL*Plus.

For example, suppose you have the following Server Manager code in one of your SQL scripts:

    # This statement returns only clerks.
    SELECT * FROM hr.employees
        WHERE job_id LIKE '%CLERK';

In SQL*Plus, replace this code with the following entry:

    -- This statement returns only clerks.
    SELECT * FROM hr.employees
        WHERE job_id LIKE '%CLERK';

### Blank Lines

Server Manager ignores blank lines within SQL statements, but when SQL*Plus encounters a blank line the default behavior is to stop recording the statement and return to the prompt.

Both products allow blank lines between distinct SQL statements. This section only applies to blank lines between clauses of SQL statements.

In SQL*Plus, the `SET SQLBLANKLINES` command alters the way blank lines are handled. When `SQLBLANKLINES` is set to `OFF`, the default setting, and there is a SQL statement containing a blank line, SQL*Plus buffers the statement at the blank line, returning to the prompt without executing the statement. This behavior allows interactive users to abort and buffer an unwanted SQL command, or to perform other SQL*Plus commands before executing or editing this buffered SQL command.

If any of your SQL scripts contain blank lines within SQL statements, then either set `SQLBLANKLINES` to `ON`, or remove the blank lines before you run these scripts using SQL*Plus.

For example, suppose you have the following SQL statement in one of your SQL scripts:

    SELECT employee_id, first_name, last_name, salary, commission_pct

        FROM hr.employees

        WHERE job_id LIKE '%MAN';

Either set `SQLBLANKLINES` to `ON`, or delete the blank lines:

    SELECT employee_id, first_name, last_name, salary, commission_pct
        FROM hr.employees
        WHERE job_id LIKE '%MAN';

If you do not remove the blank lines or set `SQLBLANKLINES` to `ON`, then SQL*Plus will treat each blank line of code as a command terminator.

The value of `SQLBLANKLINES` does not affect blank lines in PL/SQL blocks. These are always treated as part of the block and do not return to the SQL*Plus prompt.

Interactive users can terminate SQL or PL/SQL statements by entering a period on a line by itself, regardless of the value of `SQLBLANKLINES`.

### The Hyphen Continuation Character

SQL*Plus supports the use of a hyphen as a continuation character for long SQL statements or SQL*Plus commands. For example, you can use the continuation character in the following way:

    SELECT employee_id, first_name, last_name FROM hr.employees -
    WHERE job_id LIKE '%MAN';

Server Manager does not support the use of a hyphen as a continuation character, but you may use hyphens for other purposes in your SQL scripts. If you do, then SQL*Plus may interpret a hyphen as a continuation character, which can cause unexpected output.

The following sections provide scenarios in which SQL*Plus interprets the use of hyphens in SQL scripts as continuation characters, when the hyphens were meant for another purpose. Check your SQL scripts for the use of hyphens and modify them to avoid scenarios similar to those described below.

#### Hyphens Used as Dividing Lines

Your SQL scripts may use a long row of hyphens following a `REMARK` command as a dividing line in the code. Consider the following sample lines from a SQL script:

    Rem -------------------------------------------------------------------------
    SELECT employee_id, first_name, last_name, job_id
        FROM hr.employees;

In this statement, SQL*Plus interprets the first line of the `SELECT` statement as a continuation of the previous line, which is a `REMARK` comment. Therefore, the `FROM` line is interpreted as the first line of a SQL statement, and SQL*Plus returns the following error:

    unknown command beginning "FROM hr..." - rest of line ignored.

If you use hyphens as dividing lines in your SQL scripts, then remove the `REM` command preceding the hyphens before you run the scripts using SQL*Plus.

#### Hyphens Used as Minus Signs

Because the hyphen is the same keyboard character as the minus sign, you may have a hyphen at the end of a line. Consider the following sample lines from a SQL script:

    CREATE TABLE xx (
        a int,
        b int,
        c int);

    INSERT INTO xx VALUES (10, 20, 30);

    SELECT a + b -
        c FROM xx;

SQL*Plus interprets the 'c' as an alias because the minus symbol is interpreted as a continuation character:

    SELECT a + b c FROM xx;

Therefore, SQL*Plus returns the following unexpected output:

    C
    ----------
            30

Server Manager, however, interprets this code as the following:

    SELECT a + b - c FROM xx;

Therefore, Server Manager returns the following expected output:

    A+B-C
    ----------
             0

Make sure you do not have a minus sign at the end of a line in your SQL scripts.

### Ampersands

SQL*Plus interprets an ampersand (&) as a substitution variable, whereas Server Manager interprets an ampersand as a normal string. If the text following the ampersand does not have a defined value, then SQL*Plus interprets it as an undefined value and prompts the user for input, even if the ampersand is enclosed in a comment. Therefore, ampersands can cause unexpected output in SQL*Plus.

If you have SQL scripts that use ampersands as normal text strings, then you have two options:

- Use the `SET ESCAPE` command to place an escape character before each ampersand.

- Use the `SET DEFINE OFF` command to disable the recognition of substitution variables.

  Note: Do not use the `SET DEFINE OFF` command if you have other, valid substitution variables; if you do, then the other variables will not be recognized.

For example, the following SQL statement prompts the user for input in SQL*Plus:

    CREATE TABLE "Employees & Managers" (
        Employees varchar(16),
        Managers varchar(16));

    Enter value for managers:

#### Using the SET ESCAPE Command

To avoid the user prompt, you can use the `SET ESCAPE` command to set an escape character. Then, place the escape character before the ampersand. A backslash (\) is often used as an escape character.

To avoid the prompt in the preceding example by using the `SET ESCAPE` command, change the entry to the following:

    SET ESCAPE \

    CREATE TABLE "Employees \& Managers" (
        Employees varchar(16),
        Managers varchar(16));

#### Using the SET DEFINE OFF Command

To avoid the prompt in the preceding example by using the `SET DEFINE OFF` command, change the entry to the following:

    SET DEFINE OFF

    CREATE TABLE "Employees & Managers" (
        Employees varchar(16),
        Managers varchar(16));

### CREATE TYPE and CREATE LIBRARY Commands

SQL*Plus treats the `CREATE TYPE` and `CREATE LIBRARY` commands as PL/SQL blocks. Therefore, in SQL*Plus, you must use a slash (/) on a separate line to end these commands, while Server Manager allows you to end these commands with a semicolon (;).

If you end any `CREATE TYPE` or `CREATE LIBRARY` command with a semicolon in your SQL scripts, then remove the semicolon and place a slash (/) on the next line. For example, the following SQL statements are not recognized by SQL*Plus:

    CREATE OR REPLACE TYPE sys.dummy AS OBJECT (data CHAR(1));
    CREATE OR REPLACE LIBRARY DBMS_SPACE_ADMIN_LIB TRUSTED AS STATIC;

Edit these statements in the following way before you run them with SQL*Plus:

    CREATE OR REPLACE TYPE sys.aq$_dummy_t AS OBJECT (data CHAR(1))
    /
    CREATE OR REPLACE LIBRARY DBMS_SPACE_ADMIN_LIB TRUSTED AS STATIC
    /

### COMMIT Command

SQL*Plus requires that the `COMMIT` command be terminated either with a semicolon (;) or a slash (/), but Server Manager allows the `COMMIT` command with no terminator. Therefore, if you use the `COMMIT` command in your SQL scripts without a terminator, then edit these scripts to include a terminator.

For example, suppose you have the following `COMMIT` command in a SQL script:

    commit

Include a terminator for the command, as shown in either of the following examples:

    commit;

    commit
    /